<?php
	header('content-type:text/html;charset=utf-8'); 
	//重要!兩種情況中都要判斷DID是否存在,DID不存在,還要判斷工單號和機台號是否為空
	//2018-3-16 11:08:32注釋:想起來好像前端就應該做判斷過濾掉這種情況
	//$GX=$_GET["nos"];
	
	//echo $GX."<br/>";
	
	//$ldata = array();
	//$ldata=$_POST;//["nos"];
	//echo $ldata;
	$GongXu = $_POST["GongXu"];//工序
	$BanBie = $_POST["BanBie"];//班別
	
	if ($BanBie === "B" && date("G")<20){
		$DT=date("Y-m-d",strtotime("-1 day"));
	}else{
		$DT=date("Y-m-d");
	}
	
	$ZhiBiao = $_POST["ZhiBiao"];//製表人
	$Arr = $_POST["ShuZu"];//數組字串
	
	//echo $Arr."<br/>";
	$Arr = json_decode($Arr,true);//傳回來的字串,需要調整,傳回來的字串轉Json數組
	$ChanPinXingHao = "";
	$DingDanHao = "";
	$QTY = 0;
	$ISDID = "";
	$ISHID = "";
	//print_r($Arr);
	//print_r(count($Arr));
	$conn=mysql_connect("127.0.0.1:3306","root","tdled2018");
	if (!$conn){
		die('Could not connect: ' . mysql_error());
	}
	
	mysql_select_db("tddb", $conn);
	
	mysql_query("set character set 'utf8'");//读库 
	
	mysql_query("set names 'utf8'");//写库 
	
	$cnt=count($Arr);
	//
	//先查表頭ID,然後返回表頭ID,如果存在就UPDATE,不存在就INSERT INTO后取回insert_id
	$SQL = "
	SELECT 
		`HID`
	FROM 
		`d_h_temp`
	WHERE 
		`GongXu`='".$GongXu."' AND 
		`RiQi`='".$DT."' AND 
		`BanBie`='".$BanBie."' AND 
		`ZhiBiao`='".$ZhiBiao."'
	";
	$result = mysql_query($SQL);//查詢HID
	$i = mysql_num_rows($result);//i=結果集行數,小於1條就是沒有
	if ($i>0){//大於0,存在,那麼取出結果集裡面的HID并打印調試它
		while ($row = mysql_fetch_array($result)){
			$HID = $row['HID'];
		}
		// echo "SQL=".$SQL."<br/>";
		// echo "HID=".$HID."<br/>";
	}else{//否則就是小於或=0,那麼就INSERT INTO后取回insert_id
		$SQL = "
		INSERT INTO 
			`d_h_temp`( 
				`GongXu`, `RiQi`, `BanBie`, `ZhiBiao`) 
		VALUES (
			'".$GongXu."',
			'".$DT."',
			'".$BanBie."',
			'".$ZhiBiao."'
		)";
		$result = mysql_query($SQL);
		$HID = mysql_insert_id($conn);
		// echo "SQL=".$SQL."<br/>";
		// echo "HID=".$HID."<br/>";
	}
	//POD_Start
	$WaiXingChiCun = null;
	$ChanPinXiLie = null;
	$ChanPinLeiXing=null;
	$ChanPinXingHao=null;
	$TouChanQty=null;
	
	$dbName = "sqlsrv:Server=172.31.3.9,1433;Database=UFDATA_001_2017";   //emc3030
	$dbUser = "sa";    //
	$dbPassword = "TDled2017";    //
	$db = new PDO($dbName, $dbUser, $dbPassword);   //這裡是PDO鏈接方法
	
	for ($x=0; $x<$cnt; $x++) {
		//2018 03 21 加上投產數，為了 WIP 表的產生2018-3-21 22:51:03注释:已加
		//2018-3-23 18:31:39注释:实测发现工单号为无时无法运行,增加针对无的Delete和过滤
		if($Arr[$x][6] == '无' and $Arr[$x][0] != ""){
			$sql = 
			"DELETE FROM 
				`d_d_temp` 
			WHERE
				`DID`='".$Arr[$x][0]."'"
			;
			//echo $sql."</br>";
			$result = mysql_query($sql);
		}else{
			$sql = "select InvCode, OrderCode, Qty from mom_orderdetail where MoLotCode like '".substr($Arr[$x][6],0,9)."%' AND RelsDate<>''";  //2018-3-12 10:49:59注釋:用工單號從庫里取出產品型號和訂單號,審核日期不等於空白.
			
			$res = $db->query($sql);
			$InvCode = null;
			while ($row = $res->fetch()){
				$ChanPinXingHao=$row['InvCode'];//成品型號
				$DingDanHao=$row['OrderCode'];//訂單號
				$QTY=$row['Qty'];//Qty 生产订单数量 //投產數(工單量)
			}
			
			//这里判断keepwo表里面是否存在工单号
			$sql = 
			"SELECT 
				`GongDan`,
				`TouChanQty`
			FROM 
				`keepwo` 
			WHERE 
				`GongDan` = '".$Arr[$x][6]."'"
			;
			$result = mysql_query($sql);//查询工单号是否已经登记过了
			if (mysql_num_rows($result) < 1){//如果工单不存在,就进行写入
				$sql = 
				"INSERT INTO 
					`keepwo`(
						`GongDan`, 
						`ChanPinXingHao`, 
						`TouChanQty`
					) 
				VALUES 
					(
						'".$Arr[$x][6]."',
						'".$ChanPinXingHao."',
						".$QTY."
					)"
				;
			}else{
				while ( $row = mysql_fetch_array($result))  {
					//echo json_encode($row)."</br>";
					$TouChanQty=$row['TouChanQty'];
				}
				// echo $TouChanQty."</br>";
				if ($TouChanQty !== $QTY) {
					$sql = 
					"UPDATE 
						`keepwo`
					SET
						`ChanPinXingHao`='".$ChanPinXingHao."',
						`TouChanQty`='".$QTY."'
					WHERE 
						`GongDan`='".$Arr[$x][6]."'"
					;
				}
			}
			// echo $sql."</br>";
			$result = mysql_query($sql);//写入MySQL的keepwo
			
			$sz = substr($ChanPinXingHao,1,2); //成品型號 ，第2位，截2碼
			$BuChongMa = substr($ChanPinXingHao,8,1);//成品型號 ，第9位，截1碼
			//2018-3-9 16:27:52注釋:備份原語句,採用聯查補充碼形式:$sql = "select WaiXingChiCun, ChanPinLeiXing FROM `waixingdaima` WHERE`waixingdaima` ='".$sz."' ";
			//取出外形尺寸和補充碼
			$sql = 
			"SELECT 	
				`WaiXingChiCun`, `ChanPinLeiXing`, `XiLieQianZhuiMa` 
			FROM 	
				`waixingdaima`,`buchongma` 
			WHERE 	
				`WaiXingDaiMa` ='".$sz."'
			AND	
				`BuChongMa` ='".$BuChongMa."'"
			;
			
			
			$result = mysql_query($sql);//取出外形尺寸和補充碼
			
			//2018-3-8 18:40:50注釋:先搜尋補充碼,補充碼有的就直接截取補充碼表的前綴碼,如果找不到就一定是工單的第二碼.
			if (mysql_num_rows($result) < 1){
				//如果結果集行數小於1,那麼就是找不到補充碼,直接用原來的工單號第二碼形式
				$result = null;//如果沒搜到就重置結果集	
				$sql = 
				"SELECT 	
					`WaiXingChiCun`, 
					`ChanPinLeiXing` 
				FROM 	
					`waixingdaima`
				WHERE 	
					`WaiXingDaiMa` ='".$sz."'"
				;	
				$result = mysql_query($sql);
				
				while ($row = mysql_fetch_array($result)){
					$WaiXingChiCun=$row['WaiXingChiCun'];
					$ChanPinLeiXing=$row['ChanPinLeiXing'];
				}
				
				$pre=substr($Arr[$x][6],1,1);//前綴碼=工單號,第二位,截1碼(這裡需要區分常規或客製化工單號)
				
			}else{//大於等於1時,產品系列+類型+系列前綴
				while ($row = mysql_fetch_array($result)){
					$WaiXingChiCun=$row['WaiXingChiCun'];
					$ChanPinLeiXing=$row['ChanPinLeiXing'];
					$pre=$row['XiLieQianZhuiMa'];
				}
			}
			
			if ($pre == "K") { $pre ="SSC";}//如果K,就是首爾
			$ChanPinXiLie =$pre.$WaiXingChiCun; 
			//這裡是原來的循環植入
			//這裡要改成PDO取出的訂單號
			//這裡要改成PDO取出的產品型號
			//這裡要改成PDO取出的產品類型
			//這裡要改成PDO取出的產品系列
			//已改完
			if($i>0 && $Arr[$x][0]<> ""){//大於0時就有表頭數據,UPDATE
				$SQL = "
				UPDATE 
					`d_d_temp` 
				SET 
					`HID`=".$Arr[$x][1].",
					`DingDanHao`='".$DingDanHao."',
					`ChanPinXingHao`='".$ChanPinXingHao."',
					`ChanPinLeiXing`='".$ChanPinLeiXing."',
					`ChanPinXiLie`='".$ChanPinXiLie."',
					`GongDanHao`='".$Arr[$x][6]."',
					`JiTai`='".$Arr[$x][7]."',
					`JiHuaQTY`=".$Arr[$x][8].",
					`JiHuaTime`=".$Arr[$x][9].",
					`CaoZuoYuan`='".$Arr[$x][10]."',
					`ShiJiQTY`=".$Arr[$x][11].",
					`ShiJiTime`=".$Arr[$x][12].",
					`UPH`=".$Arr[$x][13].",
					`DaiJiTime`=".$Arr[$x][14].",
					`DianJianTime`=".$Arr[$x][15].",
					`HuanDanTime`=".$Arr[$x][16].",
					`HuanHaoCaiTime`=".$Arr[$x][17].",
					`QingYeJuTime`=".$Arr[$x][18].",
					`ShouJianTime`=".$Arr[$x][19].",
					`TiaoJiTime`=".$Arr[$x][20].",
					`GongChengShiYangiTime`=".$Arr[$x][21].",
					`GuZhangTime`=".$Arr[$x][22].",
					`PinZhiYiChangTime`=".$Arr[$x][23].",
					`DaiLiaoTime`=".$Arr[$x][24].",
					`ShuiDianQiYiChangiTime`=".$Arr[$x][25].",
					`QiTaJiHuaWaiTingJiTime`=".$Arr[$x][26].",
					`WeiDaChengYuanYinBeiZhu`='".$Arr[$x][27]."'
				WHERE
					`DID`=".$Arr[$x][0]
				;
				$result = mysql_query($SQL);
			}elseif($i>0 && $Arr[$x][0]===""){//小於等於0時,湊Insert多條語句,然後在PDO_End後面一次Insert
				//PHP FOR 語法:for ($x=0; $x<=10; $x++) {
				// for ($k=2;$k<=28;$k++){
					// $ISDID .= $Arr[$x][$k]."','"
				// };
				$ISDID .= "(
				'".$HID."',
				'".$DingDanHao."',
				'".$ChanPinXingHao."',
				'".$ChanPinLeiXing."',
				'".$ChanPinXiLie."',
				'".$Arr[$x][6]."',
				'".$Arr[$x][7]."',
				'".$Arr[$x][8]."',
				'".$Arr[$x][9]."',
				'".$Arr[$x][10]."',
				'".$Arr[$x][11]."',
				'".$Arr[$x][12]."',
				'".$Arr[$x][13]."',
				'".$Arr[$x][14]."',
				'".$Arr[$x][15]."',
				'".$Arr[$x][16]."',
				'".$Arr[$x][17]."',
				'".$Arr[$x][18]."',
				'".$Arr[$x][19]."',
				'".$Arr[$x][20]."',
				'".$Arr[$x][21]."',
				'".$Arr[$x][22]."',
				'".$Arr[$x][23]."',
				'".$Arr[$x][24]."',
				'".$Arr[$x][25]."',
				'".$Arr[$x][26]."',
				'".$Arr[$x][27]."'
				),"
				;
			}else{//排他后:第一次建表頭ID,建完后還是會取回HID的情況
				$ISDID.="(
				'".$HID."',
				'".$DingDanHao."',
				'".$ChanPinXingHao."',
				'".$ChanPinLeiXing."',
				'".$ChanPinXiLie."',
				'".$Arr[$x][6]."',
				'".$Arr[$x][7]."',
				'".$Arr[$x][8]."',
				'".$Arr[$x][9]."',
				'".$Arr[$x][10]."',
				'".$Arr[$x][11]."',
				'".$Arr[$x][12]."',
				'".$Arr[$x][13]."',
				'".$Arr[$x][14]."',
				'".$Arr[$x][15]."',
				'".$Arr[$x][16]."',
				'".$Arr[$x][17]."',
				'".$Arr[$x][18]."',
				'".$Arr[$x][19]."',
				'".$Arr[$x][20]."',
				'".$Arr[$x][21]."',
				'".$Arr[$x][22]."',
				'".$Arr[$x][23]."',
				'".$Arr[$x][24]."',
				'".$Arr[$x][25]."',
				'".$Arr[$x][26]."',
				'".$Arr[$x][27]."'
				),"
				;
			}
		}
		
		//这里是keepwip的开始
		$SQL = "
		SELECT
			*
		FROM
			`keepwip`
		WHERE
			`GongXu`='".$GongXu."'
		AND
			`RiQi`='".$DT."'
		AND
			`GongDan`='".$Arr[$x][6]."'"
		;
		$result = mysql_query($SQL);//查詢WIP表的唯一值是否存在
		
		$i = mysql_num_rows($result);//i=結果集行數,小於1條就是沒有
		
		$SUM = $Arr[$x][11];//SUM=JSON每行的数量
		$WCSQL = "";//完成數語句
		$LJSQL = "";//累計數語句
		$WCSUM = 0;//今日完成
		$LJSUM = 0;//累计完成
		
		if ($i>0){//大於0,存在,那麼取出結果集裡面的HID并打印調試它
			/*備份原語句
			while ($row = mysql_fetch_array($result)){
				$WCSUM = $SUM + $row['WanChengQTY'];
				$LJSUM = $SUM + $row['LeiJiQTY'];
			}
			*/
			
			//不能在查是否存在之前使用相同的變量名
			//這裡開始查詢d_d_temp的子句
			//今日完成開始
			$WCSQL = "
			SELECT
				SUM(`ShiJiQTY`) AS `SJSUM`
			FROM
				`d_d_temp` ,`d_h_temp`
			WHERE
				`d_d_temp`.`HID` =`d_h_temp`.`HID`
			AND
				`GongDanHao` = '".$Arr[$x][6]."'
			AND
				`GongXu` = '".$GongXu."'
			AND
				`RiQi` = '".$DT."'"
			;
			//echo "WCSQL=".$WCSQL."<br/>";
			
			$result = mysql_query($WCSQL);//查詢今天的d_d_temp總完成數,然後讀出并寫入到WIP表
			
			while ($row = mysql_fetch_array($result)){
				$WCSUM = $row['SJSUM'];
			}
			
			//echo "WCSUM=".$WCSUM."<br/>";
			//今日完成結束
			
			//累計完成開始
			$LJSQL = "
			SELECT
				SUM(`ShiJiQTY`) AS `SJSUM`
			FROM
				`d_d_temp` ,`d_h_temp`
			WHERE
				`d_d_temp`.`HID` =`d_h_temp`.`HID`
			AND
				`GongDanHao` = '".$Arr[$x][6]."'
			AND
				`GongXu` = '".$GongXu."'"
			;
			//echo "LJSQL=".$LJSQL."<br/>";
			
			$result = mysql_query($LJSQL);//查詢小於等於今天的d_d_temp總完成數,然後讀出并寫入到WIP表
			
			while ($row = mysql_fetch_array($result)){
				$LJSUM = $row['SJSUM'];
			}
			//echo "LJSUM=".$LJSUM."<br/>";
			//累計完成結束
			
			//子句結束
			
			/*
			SELECT 
				SUM(  `ShiJiQTY` ) AS  `SJSUM` 
			FROM  
				`d_d_temp` ,`d_h_temp`
			WHERE  
				`d_d_temp`.`HID` =`d_h_temp`.`HID`
			AND  
				`GongDanHao` =  'AD1831046'
			AND
				`GongXu` = '编带'
			AND
				`RiQi`< '2018-03-29'
			*/
			
			$SQL=
			"UPDATE 
				`keepwip`
			SET
				`WanChengQTY`='".$WCSUM."',
				`LeiJiQTY`='".$LJSUM."'
			WHERE 
				`GongXu`='".$GongXu."'
			AND
				`RiQi`='".$DT."'
			AND
				`GongDan`='".$Arr[$x][6]."'"
			;
			
		}else{//否則就是小於0,那麼就INSERTINTO
			$SQL=
			"INSERT INTO
				`keepwip`
				(
					 `GongXu`
					,`RiQi`
					,`GongDan`
					,`ChanPinXingHao`
					,`WanChengQTY`
					,`LeiJiQTY`
				)
			VALUES 
				(
					 '".$GongXu."'
					,'".$DT."'
					,'".$Arr[$x][6]."'
					,'".$ChanPinXingHao."'
					,'".$SUM."'
					,'".$SUM."'
				)
			"
			;
		}
		//echo "SQL=".$SQL."<br/>";
		$result = mysql_query($SQL);//更新或寫入wip表
		
		//这里是keepwip的结束
	}
	// echo "ISDID=".substr($ISDID,0,strlen($ISDID)-1)."<br/>";
	// echo "ISHID=".substr($ISHID,0,strlen($ISHID)-1)."<br/>";
	$ISDID=substr($ISDID,0,strlen($ISDID)-1);
	$SQL = "
	INSERT INTO 
		`d_d_temp`(`HID`, `DingDanHao`, `ChanPinXingHao`, `ChanPinLeiXing`, `ChanPinXiLie`, `GongDanHao`, `JiTai`, `JiHuaQTY`, `JiHuaTime`, `CaoZuoYuan`, `ShiJiQTY`, `ShiJiTime`, `UPH`, `DaiJiTime`, `DianJianTime`, `HuanDanTime`, `HuanHaoCaiTime`, `QingYeJuTime`, `ShouJianTime`, `TiaoJiTime`, `GongChengShiYangiTime`, `GuZhangTime`, `PinZhiYiChangTime`, `DaiLiaoTime`, `ShuiDianQiYiChangiTime`, `QiTaJiHuaWaiTingJiTime`, `WeiDaChengYuanYinBeiZhu`) 
	VALUES ".
		$ISDID
	;
	
	$result = mysql_query($SQL);
	
	$sql = 
	"SELECT 
		`DID`, 
		`d_d_temp`.`HID`, 
		`DingDanHao`, 
		`ChanPinXingHao`, 
		`ChanPinLeiXing`, 
		`ChanPinXiLie`, 
		`GongDanHao`, 
		`JiTai`, 
		`JiHuaQTY`, 
		`JiHuaTime`, 
		`CaoZuoYuan`, 
		`ShiJiQTY`, 
		`ShiJiTime`, 
		`UPH`, 
		`DaiJiTime`, 
		`DianJianTime`, 
		`HuanDanTime`, 
		`HuanHaoCaiTime`, 
		`QingYeJuTime`, 
		`ShouJianTime`, 
		`TiaoJiTime`, 
		`GongChengShiYangiTime`, 
		`GuZhangTime`, 
		`PinZhiYiChangTime`, 
		`DaiLiaoTime`, 
		`ShuiDianQiYiChangiTime`, 
		`QiTaJiHuaWaiTingJiTime`, 
		`WeiDaChengYuanYinBeiZhu`
	FROM 
		`d_h_temp`,`d_d_temp` 
	WHERE 
		`d_h_temp`.`HID` = `d_d_temp`.`HID` and `RiQi` ='".$DT."' and `GongXu` = '".$GongXu."' and `BanBie` ='".$BanBie."' and `ZhiBiao` ='".$ZhiBiao."'"
	;
	
	$result = mysql_query($sql); 
	
	$dbdata = array();
	
	//Fetch into associative array
	while ( $row = mysql_fetch_array($result))  {
		//echo json_encode($row)."</br>";
		$dbdata[]=$row;
	}
	
	//Print array in JSON format
	echo json_encode($dbdata);
	
	//echo "保存成功!";
	//echo "SQL=".$SQL."<br/>";
	//POD_End substr(string,start,length) , strlen('aaaa');
	
	/*else{//i小於0就代表沒有表頭,需要INSERT INTO 表頭內容,并讀到ID,然後湊INSERT INTO VALUES()()()()
		
		//(～￣▽￣)～ =JSON轉下面的Values的明天給老大解決...
		//2018-3-16 01:15:52:想想還是自己動手,豐衣足食...
		2018-3-17 15:50:58:表頭在前面已經處理了,那麼沒有DID的全部都要Insert into
		
	}
	*/
	
	/*
	INSERT INTO 
		`d_d_temp`(`HID`, `DingDanHao`, `ChanPinXingHao`, `ChanPinLeiXing`, `ChanPinXiLie`, `GongDanHao`, `JiTai`, `JiHuaQTY`, `JiHuaTime`, `CaoZuoYuan`, `ShiJiQTY`, `ShiJiTime`, `UPH`, `DaiJiTime`, `DianJianTime`, `HuanDanTime`, `HuanHaoCaiTime`, `QingYeJuTime`, `ShouJianTime`, `TiaoJiTime`, `GongChengShiYangiTime`, `GuZhangTime`, `PinZhiYiChangTime`, `DaiLiaoTime`, `ShuiDianQiYiChangiTime`, `QiTaJiHuaWaiTingJiTime`, `WeiDaChengYuanYinBeiZhu`) 
	VALUES 
		([value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9],[value-10],[value-11],[value-12],[value-13],[value-14],[value-15],[value-16],[value-17],[value-18],[value-19],[value-20],[value-21],[value-22],[value-23],[value-24],[value-25],[value-26],[value-27],[value-28])
		([value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9],[value-10],[value-11],[value-12],[value-13],[value-14],[value-15],[value-16],[value-17],[value-18],[value-19],[value-20],[value-21],[value-22],[value-23],[value-24],[value-25],[value-26],[value-27],[value-28])
		([value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9],[value-10],[value-11],[value-12],[value-13],[value-14],[value-15],[value-16],[value-17],[value-18],[value-19],[value-20],[value-21],[value-22],[value-23],[value-24],[value-25],[value-26],[value-27],[value-28])
	*/
?>